
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
/**
 *
 * @author Stefan.Preisig
 */
public class DbGameService implements GameService {

    private final Connection conn;

    public DbGameService() throws ClassNotFoundException, SQLException {
        this.conn = ConnectionFactory.createConnection();
    }

    @Override
    public List<Game> getGames() {
        List<Game> games = new ArrayList<>();

        // Anfrage-Statement erzeugen.
        Statement query;
        try {
            query = this.conn.createStatement();
            // idspiel, idteam1, idteam2, datum
            String sql = "SELECT * FROM tb_spiele";
            ResultSet result = query.executeQuery(sql);



            // Ergebnissätze durchfahren.
            while (result.next()) {
                games.add(instantiateGame(result));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new RuntimeException("Konnte Games nicht laden", ex);
        }

        return games;
    }

    private Game instantiateGame(ResultSet resultSetToReadFrom) throws SQLException {
        Team team1Tmp = getTeamForId(resultSetToReadFrom.getLong("idteam1"));
        Team team2Tmp = getTeamForId(resultSetToReadFrom.getLong("idteam2"));

        return new Game(team1Tmp, team2Tmp, resultSetToReadFrom.getDate("datum"));
    }

    private Game getGameForId(Long id) {
        // Anfrage-Statement erzeugen.
        Statement query;
        try {
            query = this.conn.createStatement();
            // idspiel, idteam1, idteam2, datum
            String sql = "SELECT * FROM tb_spiele AS s WHERE s.idspiel = " + id;
            ResultSet result = query.executeQuery(sql);

            Game gameTmp = null;

            while (result.next()) {
                gameTmp = instantiateGame(result);
            }

            return gameTmp;

        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new RuntimeException("Konnte Games nicht laden", ex);
        }
    }

    private Team getTeamForId(Long id) {
        try {
            Statement query = this.conn.createStatement();

            // idteam, name
            String queryString2 = "SELECT * FROM tb_teams AS t WHERE t.idteam = " + id;
            ResultSet resultSet2 = query.executeQuery(queryString2);
            Team teamTmp = null;
            while (resultSet2.next()) {
                teamTmp = new Team(resultSet2.getString("name"));
            }

            return teamTmp;

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Konnte Team nicht laden: " + id, ex);
        }
    }

    private Team instantiateTeam(ResultSet resultSetToReadFrom) throws SQLException {
        return new Team(resultSetToReadFrom.getString("name"));
    }

    @Override
    public List<Team> getTeams() {
        List<Team> teams = new ArrayList<>();

        try {
            Statement query = this.conn.createStatement();

            // idteam, name
            String queryString = "SELECT * FROM tb_teams";
            ResultSet resultSet = query.executeQuery(queryString);

            while (resultSet.next()) {
                teams.add(instantiateTeam(resultSet));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Konnte Teams nicht laden", ex);
        }

        return teams;
    }

    @Override
    public void placeBet(Bet bet) {
        try {
            Statement query = this.conn.createStatement();

            // user_id, pwd_md5, idspiel, result1, result2              
            String queryString2 = "SELECT fn_tipp_abgeben (" + bet.getUser().getId() + ",null," + bet.getGame().getId() + "," + bet.getHomeTeamScore() + "," + bet.getGuestTeamScore() + ")";
            ResultSet resultSet2 = query.executeQuery(queryString2);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Tipp konnte nicht abgegeben werden: " + bet.toString(), ex);
        }
    }

    @Override
    public List<Bet> getBets(User user) {
        List<Bet> bets = new ArrayList<>();

        try {
            Statement query = this.conn.createStatement();

            // idtipp, userid,idspiel,result1,result2,timestamp
            String queryString = "SELECT * FROM tb_tipp";
            ResultSet resultSet = query.executeQuery(queryString);

            while (resultSet.next()) {
                bets.add(instantiateBet(resultSet));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Konnte Teams nicht laden");
        }

        return bets;
    }

    @Override
    public Map<String, Integer> getHighscore() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    private User getUserForId(long id) {
        try {
            Statement query = this.conn.createStatement();

            // id, username, password, forname,lastname,email,credits
            String queryString = "SELECT * FROM tb_user AS u WHERE u.id = " + id;
            ResultSet resultSet = query.executeQuery(queryString);
            User userTmp = null;
            while (resultSet.next()) {
                userTmp = new User(resultSet.getString("username"), resultSet.getString("forname"), resultSet.getString("lastname"), resultSet.getString("email"));
            }

            return userTmp;

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Konnte User nicht laden: " + id, ex);
        }
    }

    private Bet instantiateBet(ResultSet resultSet) throws SQLException {
        return new Bet(getUserForId(resultSet.getLong("userid")), getGameForId(resultSet.getLong("idspiel")), resultSet.getInt("result1"), resultSet.getInt("result2"));
    }

    @Override
    public void finalize() {
        if (this.conn != null) {
            try {
                this.conn.close();
                System.out.println("closed");
            } catch (Exception ex) {
            }
        }
    }
}
